package by.bsu.rfe.belianko.euniversity.constants;

/**
 * Class that stores SQL Queries.
 * 
 * @author Vitali_Belianko
 * 
 */
public final class SQLQueries {
	public static final String ADD_USER = "INSERT INTO users ( login, password,power) VALUES (?,?,'student')";
	public static final String ADD_STUDENT = "INSERT INTO students (user_id,name) VALUES((SELECT id FROM users WHERE login=?),?)";
	public static final String CHECK_STUDENT = "SELECT id FROM users WHERE login = ?";
	public static final String CHECK_FOR_ADMIN = "SELECT password,power FROM users WHERE login=?";
	public static final String GET_STUDENT_BY_LOGIN = "SELECT u.id, u.power, s.name,s.total_score,s.faculty_id,s.approved,s.accepted FROM users u INNER JOIN students s ON u.id=s.user_id  WHERE login = ?";
	public static final String GET_FACULTY_LIST = "SELECT * FROM faculties";
	public static final String ENROLL_ON_FACULTY = "UPDATE students SET faculty_id=?,total_score=? WHERE user_id=?";
	public static final String GET_FACULTY_BY_ID = "SELECT * FROM faculties WHERE faculty_id=?";
	public static final String GET_NOT_APPROVED_STUDENTS = "SELECT id,name,total_score, approved, faculty_name FROM students JOIN faculties ON students.faculty_id=faculties.faculty_id WHERE students.faculty_id IS NOT NULL AND students.approved IS NULL";
	public static final String APPROVE_STUDENT = "UPDATE students SET approved='true' WHERE id=?";
	public static final String ADD_TO_SUBMITTED = "UPDATE faculties SET submitted=submitted+1 WHERE faculty_id=(SELECT faculty_id FROM students WHERE id=?)";
	public static final String ACCEPT_STUDENT = "UPDATE students SET accepted='true' WHERE faculty_id=? AND students.approved IS NOT NULL ORDER BY total_score desc limit ?";
	public static final String GET_ACCEPTED_STUDENTS = "SELECT name,total_score,faculty_name FROM students  JOIN faculties ON students.faculty_id=faculties.faculty_id  WHERE accepted IS NOT NULL ORDER BY faculty_name";
	public static final String GET_ADMISSION_STATE = "SELECT state FROM admission WHERE year=2013";
	public static final String COMPLETE_RECEIVING = "UPDATE admission SET state='complete'";

	private SQLQueries() {
	}
}
